Click Here!
home account info subscribe login search My ITKnowledge FAQ/help site map contact us


 
Brief Full
 Advanced
      Search
 Search Tips
To access the contents, click the chapter and section titles.

Oracle Performance Tuning and Optimization
(Publisher: Macmillan Computer Publishing)
Author(s): Edward Whalen
ISBN: 067230886x
Publication Date: 04/01/96

Bookmark It

Search this book:
 
Previous Table of Contents Next


Tuning the Private SQL and PL/SQL Areas

A private SQL area is an area in memory that contains binding information and runtime buffers. Every session that issues SQL statements has a private SQL area; reducing these resources can be very effective when large numbers of users are involved. A private SQL area is further segmented into a persistent area and a runtime area.

The persistent area contains binding information used during the query for data input and retrieval. The size of this area depends on the number of binds and the number of columns specified in the statement.

The runtime area contains information used while the SQL statement is being executed. The size of the runtime area depends on the complexity and type of SQL statements being issued and the size of the rows being processed. The runtime area is freed after the statement has been executed. In a query operation, the runtime area is freed only after all the rows have been fetched.

In a dedicated server, the private SQL area is located in the user’s PGA. In the case of a multithreaded server, the persistent areas and (for SELECT statements) the runtime areas are kept in the SGA.

It is important to make sure that each user can allocate enough memory for his or her private SQL area; in the case of the multithreaded server, there must be sufficient space in the SGA to connect the required number of users.

Cursors can be used in precompilers to improve performance by reducing the frequency of parsing. To take advantage of cursors, it may be necessary to increase the value of the Oracle parameter OPEN_CURSORS.

The size of memory needed for each user is determined by the application and tunables such as OPEN_CURSORS. This memory is allocated automatically. In some operating systems, the amount of memory that can be allocated per user is controlled by system parameters. If your application or tuning changes the amount of memory for each user process, you may have to increase the value of those OS parameters. You may also have to increase the amount of system RAM or reduce the Oracle instance memory usage to avoid swapping or paging.

Tuning the Shared Pool

To tune the shared pool, you must look at the individual parts of the shared pool. The shared pool contains both the library cache and the data dictionary cache. In a multithreaded server, the shared pool is also used to store session information.

Library Cache

The library cache contains the shared SQL and PL/SQL areas. Performance can be improved by both increasing the cache-hit rate in the library cache and by speeding access to the library cache by holding infrequently used SQL statements in cache longer.

A cache miss in the shared SQL area occurs when either a parse statement is called and the already parsed statement does not already exist in the shared SQL area or when an application tries to execute an SQL statement and the shared SQL area containing the parsed statement has been deallocated from the library cache.

For an SQL statement to take advantage of SQL or PL/SQL statements that may have already been parsed, the following criteria must be met:

  The text of the SQL statement must be identical to the SQL statement that has already been parsed. This includes whitespaces.
  References to schema objects in the SQL statements must resolve to the same object.
  Bind variables must match the same name and data type.
  The SQL statements must be optimized using the same approach; in the case of the cost-base approach, the same optimization goal must be used.

At first glance, you may think that many of these conditions make it difficult to take advantage of the shared SQL areas. But users sharing the same application code can easily take advantage of already parsed shared SQL statements. It is to the advantage of the application developer to use the same SQL statements to access the same data and thus ensure that SQL statements within the application can also take advantage of this caching.

Using stored procedures whenever possible guarantees that the same shared PL/SQL area is used. Another advantage is that stored procedures are stored in a parsed form, eliminating runtime parsing altogether.


TIP:  Standardizing on naming conventions for bind variables and on spacing conventions for SQL and PL/SQL statements also increases the likelihood of reusing shared SQL statements.

The V$LIBRARYCACHE table contains statistics on how well you are utilizing the library cache. The important columns to view in this table are PINS and RELOADS.

  PINS: The number of times the item in the library cache was executed.
  RELOADS: The number of times the library cache misses and the library object must be reloaded.

A low number of reloads relative to the number of executions indicates a high cache-hit rate. To get an idea of the total number of cache misses, use this statement:

SQL> SELECT SUM(reloads) "Cache Misses",
  2  SUM(pins) "Executions",
  3  100 * ( SUM(reloads) / SUM(pins) ) "Cache Miss Percent"
  4  FROM v$librarycache;

Cache Misses Executions Cache Miss Percent
------------ ---------- ------------------
           9       2017          .44620724

The sample output shown here indicates that a total of 2,017 SQL statements, PL/SQL blocks, and object definitions were accessed with only 9 having to reload because they had aged out of the library cache. This means that only .44 percent of these statements resulted in reparsing.

To look at the cache hits based on the types of statements, you can use the following statement:

SQL> SELECT namespace,
  2  reloads "Cache Misses",
  3  pins "Executions"
  4  FROM v$librarycache;

NAMESPACE       Cache Misses Executions
-------------- ------------  ----------
SQL AREA                   4       1676
TABLE/PROCEDURE            5        309
BODY                       0          0
TRIGGER                    0          0
INDEX                      0         21
CLUSTER                    0         15
OBJECT                     0          0
PIPE                       0          0

8 rows selected.

The total amount of reloads should be near zero. If you see more than 1 percent library cache misses, take action. You can reduce the number of cache misses by writing identical SQL statements or by increasing the size of the library cache.

You should be able to reduce the library cache misses by increasing the amount of memory available for the library cache. This can be done by increasing the Oracle tunable parameter SHARED_POOL_SIZE.

You may also have to increase the number of cursors available for a session by increasing the Oracle parameter OPEN_CURSORS.


Previous Table of Contents Next


Products |  Contact Us |  About Us |  Privacy  |  Ad Info  |  Home

Use of this site is subject to certain Terms & Conditions, Copyright © 1996-2000 EarthWeb Inc.
All rights reserved. Reproduction whole or in part in any form or medium without express written permission of EarthWeb is prohibited.